﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SMTC.Model.Domain;
using System.Data.Common;
using IMPlant.Core.Data.Unity;
using System.Data;

namespace SMTC.Dao.Plan
{
    public class PlanProcessDao : BaseDao
    {
        /// <summary>
        /// 插入计划头信息
        /// </summary>
        private const string SQL_INSERT_PLAN_HEADER = @"INSERT INTO dbo.SMT_PlanHead (
	                                                        PlanID,
	                                                        xSVMaterial,
	                                                        BusinessUnit,
	                                                        Plant,
	                                                        PlanVersion,
                                                            Operation,
                                                            OperationTime
                                                        ) VALUES ( 
	                                                        /* PlanID - uniqueidentifier */ @PlanID,
	                                                        /* xSVMaterial - nvarchar(20) */ @xSVMaterial,
	                                                        /* BusinessUnit - nvarchar(20) */ @BusinessUnit,
	                                                        /* Plant - nvarchar(50) */ @Plant,
	                                                        /* PlanVersion - nvarchar(20) */@PlanVersion ,
                                                             @Operation,@OperationTime)";
        /// <summary>
        /// 插入计划明细信息
        /// </summary>
        private const string SQL_INSERT_PLAN_DETAIL = @"INSERT INTO dbo.SMT_PlanDetail (
		                                                PlanDetailID,
		                                                PlanID,
		                                                xSVMateria,
		                                                PlanYear,
		                                                PlanMonth,
		                                                PlanMount
	                                                ) VALUES ( 
		                                                /* PlanDetailID - nchar(10) */ NEWID(),
		                                                /* PlanID - uniqueidentifier */ @PlanID,
		                                                /* xSVMateria - nvarchar(20) */ @xSVMateria,
		                                                /* PlanYear - int */ @PlanYear,
		                                                /* PlanMonth - int */ @PlanMonth,
		                                                /* PlanMount - int */ @PlanMount )";
        /// <summary>
        /// 删除计划头信息
        /// </summary>
        private const string SQL_DELETE_PLAN_HEADER = @"DELETE dbo.SMT_PlanHead WHERE PlanID = @PlanID";
        /// <summary>
        /// 删除计划明细信息
        /// </summary>
        private const string SQL_DELETE_PLAN_DETAIL = @"DELETE dbo.SMT_PlanDetail WHERE PlanID = @PlanID";
        /// <summary>
        /// 更新头信息
        /// </summary>
        private const string SQL_UPDATE_PLAN_HEADER = @"UPDATE dbo.SMT_PlanHead SET xSVMaterial = @xSVMaterial
                                                               ,Operation = @Operation 
                                                               ,OperationTime = @OperationTime
                                                        WHERE PlanID = @PlanID";
        /// <summary>
        /// 更新计划信息
        /// </summary>
        private const string SQL_UPDATE_PLAN_DETAIL = @"UPDATE dbo.SMT_PlanDetail SET PlanMount = @PlanMount
                                                        WHERE PlanID = @PlanID AND PlanMonth = @PlanMonth
                                                              ";
        private const string SQL_GETPLANVERSION = @"SELECT DISTINCT PlanVersion FROM dbo.SMT_PlanHead ORDER BY PlanVersion DESC";

        private const string SQL_INSERT_COPYVERSION = @"INSERT INTO dbo.SMT_PlanHead (
	                                                        PlanID,
	                                                        xSVMaterial,
	                                                        BusinessUnit,
	                                                        Plant,
	                                                        PlanVersion
                                                        ) 
                                                        SELECT PlanID,xSVMaterial,BusinessUnit,Plant,
	                                                           @CopyToVersion 
                                                        FROM dbo.SMT_PlanHead WHERE PlanVersion = @CopyVerson";

        private const string SQL_DELETE_PLAN_DETAIL_BYVersion = @"DELETE dbo.SMT_PlanDetail WHERE PlanID IN 
                                                        (
                                                          SELECT PlanID FROM dbo.SMT_PlanHead WHERE PlanVersion = @PlanVersion
                                                        )
                                                        ";

        private const string SQL_DELETE_PLAN = @"DELETE dbo.SMT_PlanHead WHERE PlanVersion = @PlanVersion";

        public bool DeletePlanByVersion(string PlanVersion,ref DbTransaction dbt)
        {
            DbTransaction localDbt = null;
            if (dbt == null)
            {
                dbt = DBTemplete.CreateDbTransaction();
            }
            localDbt = dbt;

            DbParameter[] parameter = 
            {
                DBparameterBuild.Build("@PlanVersion",PlanVersion)
            };

            return DBTemplete.ExcuteNoQuery(CommandType.Text, SQL_DELETE_PLAN, parameter,dbt) > 0;
        }

        public bool DeletePlanDetailByVersion(string PlanVersion, ref DbTransaction dbt)
        {
            DbTransaction localDbt = null;
            if (dbt == null)
            {
                dbt = DBTemplete.CreateDbTransaction();
            }
            localDbt = dbt;

            DbParameter[] parameter = 
            {
                DBparameterBuild.Build("@PlanVersion",PlanVersion)
            };

            return DBTemplete.ExcuteNoQuery(CommandType.Text, SQL_DELETE_PLAN_DETAIL_BYVersion, parameter,dbt) > 0;
        }

        public bool CopyVersion(string OldVerson, string CopyToVersion)
        {
            DbParameter[] parameter = 
            {
                DBparameterBuild.Build("@CopyToVersion",CopyToVersion)
                ,DBparameterBuild.Build("@CopyVerson",OldVerson)
            };

            return DBTemplete.ExcuteNoQuery(CommandType.Text, SQL_INSERT_COPYVERSION,parameter) > 0;
        }

        /// <summary>
        /// 从大到小排序获取版本
        /// </summary>
        /// <returns></returns>
        public List<PlanVersionDomain> GetPlanVersionListDesc()
        {
            return DBTemplete.ExcuteGetList<PlanVersionDomain>(CommandType.Text, SQL_GETPLANVERSION);
        }

        /// <summary>
        /// 插入计划头信息
        /// </summary>
        /// <param name="item"></param>
        /// <param name="dbt"></param>
        /// <returns></returns>
        public bool InsertPlanHeader(ImportPlanDomain item, ref DbTransaction dbt)
        {
            DbTransaction localDbt = null;
            if (dbt == null)
            {
                dbt = DBTemplete.CreateDbTransaction();
                localDbt = dbt;
            }
            else
            {
                localDbt = dbt;
            }

            DbParameter[] parameter = 
            {
                DBparameterBuild.Build("@PlanID",item.ID)
                ,DBparameterBuild.Build("@xSVMaterial",item.MaterialCode)
                ,DBparameterBuild.Build("@BusinessUnit",item.BU)
                ,DBparameterBuild.Build("@Plant","")
                ,DBparameterBuild.Build("@PlanVersion",item.PlanVersion)
                ,DBparameterBuild.Build("@Operation",item.Operation)
                ,DBparameterBuild.Build("@OperationTime",item.OperationTime)
            };

            return DBTemplete.ExcuteNoQuery(CommandType.Text, SQL_INSERT_PLAN_HEADER, parameter, dbt) > 0;
        }
        /// <summary>
        /// 插入计划明细信息
        /// </summary>
        /// <param name="item"></param>
        /// <param name="month"></param>
        /// <param name="dbt"></param>
        /// <returns></returns>
        public bool InsertPlanDetail(ImportPlanDomain item, int month, ref DbTransaction dbt)
        {
            DbTransaction localDbt = null;
            if (dbt == null)
            {
                dbt = DBTemplete.CreateDbTransaction();
                localDbt = dbt;
            }
            else
            {
                localDbt = dbt;
            }

            DbParameter[] parameter = 
            {
                DBparameterBuild.Build("@PlanID",item.ID)
                ,DBparameterBuild.Build("@xSVMateria",item.MaterialCode)
                ,DBparameterBuild.Build("@PlanYear",DateTime.Now.Year)
                ,DBparameterBuild.Build("@PlanMonth",month)
                ,DBparameterBuild.Build("@PlanMount",this.GetMountByMonth(item,month))
            };

            try
            {
                return DBTemplete.ExcuteNoQuery(CommandType.Text, SQL_INSERT_PLAN_DETAIL, parameter, dbt) > 0;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 删除计划头信息
        /// </summary>
        /// <param name="planId"></param>
        /// <param name="dbt"></param>
        /// <returns></returns>
        public bool DeletePlanHeader(string planId,ref DbTransaction dbt)
        {
            DbTransaction localDbt = null;
            if (dbt == null)
            {
                dbt = DBTemplete.CreateDbTransaction();
                localDbt = dbt;
            }
            else
            {
                localDbt = dbt;
            }

            DbParameter[] parameters = { DBparameterBuild.Build("@PlanID", planId) };

            return DBTemplete.ExcuteNoQuery(CommandType.Text, SQL_DELETE_PLAN_HEADER, parameters, dbt) > 0;
        }
        /// <summary>
        /// 删除计划明细信息
        /// </summary>
        /// <param name="planId"></param>
        /// <param name="dbt"></param>
        /// <returns></returns>
        public bool DeletePlanDetail(string planId, ref DbTransaction dbt)
        {
            DbTransaction localDbt = null;
            if (dbt == null)
            {
                dbt = DBTemplete.CreateDbTransaction();
                localDbt = dbt;
            }
            else
            {
                localDbt = dbt;
            }

            DbParameter[] parameters = { DBparameterBuild.Build("@PlanID", planId) };

            return DBTemplete.ExcuteNoQuery(CommandType.Text, SQL_DELETE_PLAN_DETAIL, parameters, dbt) > 0;
        }
        /// <summary>
        /// 更新头信息
        /// </summary>
        /// <param name="item"></param>
        /// <param name="dbt"></param>
        /// <returns></returns>
        public bool UpdatePlanHeader(ImportPlanDomain item, ref DbTransaction dbt)
        {
            DbTransaction localDbt = null;
            if (dbt == null)
            {
                dbt = DBTemplete.CreateDbTransaction();
                localDbt = dbt;
            }
            else
            {
                localDbt = dbt;
            }

            DbParameter[] parameters = { 
                                           DBparameterBuild.Build("@PlanID", item.ID) 
                                          ,DBparameterBuild.Build("@xSVMaterial", item.MaterialCode) 
                                              ,DBparameterBuild.Build("@Operation", item.Operation)
                                              ,DBparameterBuild.Build("@OperationTime", item.OperationTime)
                                       };

            return DBTemplete.ExcuteNoQuery(CommandType.Text, SQL_UPDATE_PLAN_HEADER, parameters, dbt) > 0;
        }
        /// <summary>
        /// 更新明细信息
        /// </summary>
        /// <param name="item"></param>
        /// <param name="planmonth"></param>
        /// <param name="dbt"></param>
        /// <returns></returns>
        public bool UpdatePlanDetail(ImportPlanDomain item, int planmonth,ref DbTransaction dbt)
        {
            DbTransaction localDbt = null;
            if (dbt == null)
            {
                dbt = DBTemplete.CreateDbTransaction();
                localDbt = dbt;
            }
            else
            {
                localDbt = dbt;
            }

            DbParameter[] parameters = { 
                                           DBparameterBuild.Build("@PlanID", item.ID) 
                                          ,DBparameterBuild.Build("@PlanMonth", planmonth)
                                          ,DBparameterBuild.Build("@PlanMount", this.GetMountByMonth(item,planmonth))
                                       };

            return DBTemplete.ExcuteNoQuery(CommandType.Text, SQL_UPDATE_PLAN_DETAIL, parameters, dbt) > 0;
        }


        /// <summary>
        /// 根据月份，获取该月份数据结构上的需求数据
        /// </summary>
        /// <param name="item"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        private int GetMountByMonth(ImportPlanDomain item, int month)
        {
            switch (month)
            {
                case 1: return item.m1;
                case 2: return item.m2;
                case 3: return item.m3;
                case 4: return item.m4;
                case 5: return item.m5;
                case 6: return item.m6;
                case 7: return item.m7;
                case 8: return item.m8;
                case 9: return item.m9;
                case 10: return item.m10;
                case 11: return item.m11;
                case 12: return item.m12;
                default: throw new Exception("月份不存在");
            }
        }
        /// <summary>
        /// 根据月份，获取该月份数据结构上的需求数据-参数
        /// </summary>
        /// <param name="item"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        private float GetMountByMonthPara(ImportPlanDomain item, int month)
        {
            switch (month)
            {
                case 1: return item.p1;
                case 2: return item.p2;
                case 3: return item.p3;
                case 4: return item.p4;
                case 5: return item.p5;
                case 6: return item.p6;
                case 7: return item.p7;
                case 8: return item.p8;
                case 9: return item.p9;
                case 10: return item.p10;
                case 11: return item.p11;
                case 12: return item.p12;
                default: throw new Exception("月份不存在");
            }
        }


    }
}
